using System.Data;
using Framework.Library.Components.Commands;
using Framework.Library.Components.Modules;
using Framework.Library.Components.Portal;
using Framework.Library.Components.Portal.Data;
using Framework.Library.Components.Roles;
using Framework.Library.Components.Tabs;
using Framework.Library.Components.Users;
using Framework.Library.Helper;
using Framework.Library.Utilities;
using System;
using System.Text;
using System.Data.SqlClient;

namespace Framework.Library.Components.Providers
{
	public class SqlDataProvider : DataAccessProvider
	{
		private string ConnectionString;
		public SqlDataProvider(string connStr)
		{
			this.ConnectionString = connStr;
        }        

        #region Portals
        public override PortalSetting GetPortalSetting()
        {
            PortalSetting setting = new PortalSetting();
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_PortalSettings_GetSettings");
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dtSettings = ds.Tables[0];
                foreach (DataRow row in dtSettings.Rows)
                {
                    setting[row["SettingName"].ToString()] = row["SettingValue"].ToString();
                }
            }
            return setting;
        }
        public override void SetPortalSetting(string settingName, string settingValue)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_PortalSettings_SetValue", settingName, settingValue);
        }
        public override void SetPortalSetting(string settingName, string settingValue, string param1, string param2)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_PortalSettings_SetValueWidthParams", settingName, settingValue, param1, param2);
        }
        public override Portal_PortalsInfo GetPortalsInfoByLang(int portalId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Portals_GetInfoByLang", portalId, AppEnv.GetLanguage());
            Portal_PortalsInfo portalInfo = (Portal_PortalsInfo)ObjectHelper.CreateObject(typeof(Portal_PortalsInfo), dr);
            return portalInfo;
        }
        public override DataTable GetPortals()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAll");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetPortalsByUserID(int _userid)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByUserID", _userid);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetPortalsByLang(string _lang)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByLang", _lang);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetPortalsByLang(string _lang, int level, int position)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByLangByLevel", _lang, level, position);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }        
        public override DataTable GetPortalsByLangLeft(string _lang, int level, int position)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByLangByLevelLeft", _lang, level, position);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetPortalsByLangParent(string _lang, int Portal_ParentId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByLangByLevelParent", _lang, Portal_ParentId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }        
        public override Portal_PortalsInfo GetPortalsById(int _portalId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Portals_GetAllById", _portalId);
            Portal_PortalsInfo portalInfo = (Portal_PortalsInfo)ObjectHelper.CreateObject(typeof(Portal_PortalsInfo), dr);
            return portalInfo;
        }
        public override DataTable GetPortalsByParentId(int _parentId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByParentId", _parentId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetPortalsByParentId(int userId, int _parentId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Portals_GetAllByParentIdAndUserId", userId, _parentId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override void DeletePortal(int portalId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Portals_Delete", portalId);
        }
        public override int AddPortal(Portal_PortalsInfo portal)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Portals_Insert", portal.Portal_ParentId, portal.Name, portal.Description, portal.Visible, portal.Enable, portal.Language, portal.ImageUrl, portal.Priority);
        }
        public override void UpdatePortal(Portal_PortalsInfo portal)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Portals_Update", portal.PortalID, portal.Portal_ParentId, portal.Name, portal.Description, portal.Visible, portal.Enable, portal.Language, portal.ImageUrl, portal.Priority);
        }
        #endregion

        #region Module
        public override DataTable GetModules(string Name, string Folder)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Modules_GetAll", Name, Folder);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetModules()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Modules_GetAll_");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override int AddModule(ModuleInfo module)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Modules_Insert", module.ModuleName, module.ModuleTitle, module.ModuleDescription, module.ModuleFolder);
        }
        public override void UpdateModule(ModuleInfo module)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Modules_Update", module.ModuleID, module.ModuleName, module.ModuleTitle, module.ModuleDescription, module.ModuleFolder);
        }
        public override void DeleteModule(int moduleId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Modules_Delete", moduleId);
        }
        public override ModuleInfo GetModule(int moduleId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Modules_GetInfo", moduleId);
            ModuleInfo module = (ModuleInfo)ObjectHelper.CreateObject(typeof(ModuleInfo), dr);
            return module;

        }
        public override ModuleSetting GetModuleSettings(int moduleId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Modules_GetModuleSettings", moduleId);
            ModuleSetting setting = new ModuleSetting();
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dtSettings = ds.Tables[0];
                foreach (DataRow row in dtSettings.Rows)
                {
                    setting[row["SettingName"].ToString()] = row["SettingValue"].ToString();
                }
            }
            return setting;
        }
        public override void SetModuleSetting(int moduleId, string name, string value)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Modules_SetModuleSettings", moduleId, name, value);
        }
        #endregion

        #region ModuleControls
        public override DataTable GetModuleControls()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_ModuleControls_GetAll");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetModuleControlsForCommand()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_ModuleControls_GetForCommand");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetModuleControlsForWeb()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_ModuleControls_GetForWeb");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetModuleControls(int moduleId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_ModuleControls_GetByModule", moduleId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetModuleControls(int tabId, string layoutPosition)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_ModuleControls_GetByTabIDAndLayoutPosition", tabId, layoutPosition);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override int AddModuleControl(ModuleControlInfo moduleControl)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_ModuleControls_Insert", moduleControl.ModuleID,
                        moduleControl.ControlName, moduleControl.ControlTitle, moduleControl.ControlKey, moduleControl.ControlPath,
                  moduleControl.ControlRole, moduleControl.ControlIcon, moduleControl.ControlDescription, moduleControl.ControlOrder,
                                                 moduleControl.ControlHeader);
        }
        public override void UpdateModuleControl(ModuleControlInfo moduleControl)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_ModuleControls_Update", moduleControl.ControlID, moduleControl.ModuleID, moduleControl.ControlName,
                                     moduleControl.ControlTitle, moduleControl.ControlKey, moduleControl.ControlPath, moduleControl.ControlRole,
                                                                                     moduleControl.ControlIcon, moduleControl.ControlDescription, moduleControl.ControlOrder, moduleControl.ControlHeader);
        }
        public override void DeleteModuleControl(int controlId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_ModuleControls_Delete", controlId);
        }
        public override ModuleControlInfo GetModuleControl(int controlId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_ModuleControls_GetInfo", controlId);
            ModuleControlInfo control = (ModuleControlInfo)ObjectHelper.CreateObject(typeof(ModuleControlInfo), dr);
            return control;
        }
        public override ModuleControlInfo GetModuleControl(string controlKey)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_ModuleControls_GetInfoByControlKey", controlKey);
            ModuleControlInfo control = (ModuleControlInfo)ObjectHelper.CreateObject(typeof(ModuleControlInfo), dr);
            return control;
        }
        public override bool CheckExistsControlKey(string controlKey)
        {
            int result = (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_ModuleControls_CheckExistsControlKey", controlKey);
            return result > 0;
        }
        #endregion

        #region Roles
        public override DataTable GetRoles()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Roles_GetAll");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetRoles(string key, string searchby, string filter)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Roles_Search", key, searchby, filter);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetRoles(int userId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Roles_GetRolesForUser", userId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override int AddRole(RoleInfo role)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Roles_Insert", role.RoleName, role.RoleDescription, role.RoleParentID);
        }
        public override void UpdateRole(RoleInfo role)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_Update", role.RoleID, role.RoleName, role.RoleDescription, role.RoleParentID);
        }
        public override void DeleteRole(int roleId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_Delete", roleId);
        }
        public override RoleInfo GetRole(int roleId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Roles_GetInfo", roleId);
            RoleInfo role = (RoleInfo)ObjectHelper.CreateObject(typeof(RoleInfo), dr);
            return role;
        }
        public override DataTable GetRolesByParentId(int parentId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Roles_GetRolesByParentId", parentId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override void UpdateUserToRole(int userId, int roleId, int portalId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_UpdateUserToRoleAndPortal", userId, roleId, portalId);
        }
        public override void AddUserToRole(int userId, int roleId, int portalId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_AddUserToRoleAndPortal", userId, roleId, portalId);
        }
        public override void RemoveUserFromRole(int userId, int roleId, int portalId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_RemoveUserFromRoleAndPortalID", userId, roleId, portalId);
        }
        public override void AddCommandToRoleByPortalID(int commandId, int roleId, int portalID)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_AddCommandToRoleByPortalID", commandId, roleId, portalID);
        }       
        public override void RemoveCommandFromRoleByPortalID(int commandId, int roleId, int portalID)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_RemoveCommandFromRoleByPortalID", commandId, roleId, portalID);
        }
        public override void UpdateRolePermissionByPortalID(int commandId, int roleId, int portalId, bool Role_View, bool Role_Add, bool Role_Update, bool Role_Delete)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Roles_UpdateCommandFromRoleByPortalID", commandId, roleId, portalId, Role_View, Role_Add, Role_Update, Role_Delete);
        }
        #endregion

        #region PortalCache
        public override int AddPortalCache(Portal_CacheInfo portal)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Cache_Insert", portal.PortalID, portal.ZoneID, portal.CacheKey);
        }
        public override void DeletePortalCache(Portal_CacheInfo info)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Cache_Delete", info.PortalID, info.ZoneID, info.Portal_Cache_ID);
        }
        public override DataTable GetPortalCacheByPortalIdAndZoneID(int _portalId, int zoneid)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Cache_GetAll", _portalId, zoneid);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        #endregion

        #region Users
        public override DataTable GetUsers()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Users_GetAll");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetAllByPortal(int PortalID)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Users_GetAllByPortal", PortalID);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetUsers(string key, string searchby, string filter, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Users_Search", key, searchby, filter, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetUsersByUser(string key, string searchby, string filter, int userid, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Users_Search_By_User", key, searchby, filter, userid, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetUsers(int roleId, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Users_GetUsersInRole", roleId, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetUsersNotInRole(int roleId, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Users_GetUsersNotInRole", roleId, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override UserInfo ValidateUser(string username, string password)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Users_ValidateUser", username, password);
            UserInfo user = (UserInfo)ObjectHelper.CreateObject(typeof(UserInfo), dr);
            return user;
        }
        public override int AddUser(UserInfo user)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Users_Insert", user.Username, user.Password,
                                                 user.Name, user.DisplayName, user.Email, user.Tel, user.Mobile,
                                                 user.Address, user.IsSuperAdmin, user.Joined, user.LastVisit, user.IP, user.TimeZone, user.IsApproved, user.Suspended, user.PMNotification);
        }
        public override void EditUser(UserInfo user)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_Edit", user.UserID, user.Username, user.Password,
                                                 user.Name, user.DisplayName, user.Email, user.Tel, user.Mobile,
                                                 user.Address, user.IsSuperAdmin, user.IP, user.TimeZone, user.IsApproved, user.Suspended, user.PMNotification);
        }

        public override void UpdateUser(UserInfo user)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_Update", user.UserID, user.Name, user.DisplayName, user.Email, user.Tel, user.Mobile,
                                                 user.Address, user.IsSuperAdmin, user.IP, user.TimeZone, user.PMNotification);
        }
        public override void DeleteUser(int userId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_Delete", userId);
        }
        public override UserInfo GetUser(int userId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Users_GetInfo", userId);
            UserInfo user = (UserInfo)ObjectHelper.CreateObject(typeof(UserInfo), dr);
            return user;
        }
        public override UserInfo GetUser(string username)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Users_GetInfoByUsername", username);
            UserInfo user = (UserInfo)ObjectHelper.CreateObject(typeof(UserInfo), dr);
            return user;
        }
        public override void AddCommandToUser(int commandId, int userId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_AddCommandToUser", commandId, userId);
        }
        public override void RemoveCommandFromUser(int commandId, int userId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_RemoveCommandFromUser", commandId, userId);
        }
        public override void ResetPassword(int userId, string password)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_ResetPassword", userId, password);
        }
        public override void ChangePassword(int userId, string oldPassword, string newPassword)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_ChangePassword", userId, oldPassword, newPassword);
        }
        public override void AddCommandToUserByPortalID(int commandId, int userId, int portalId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_AddCommandToUserByPortalID", commandId, userId, portalId);
        }
        public override void RemoveCommandFromUserByPortalID(int commandId, int userId, int portalId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_RemoveCommandFromUserByPortalID", commandId, userId, portalId);
        }
        public override void UpdateUserPermissionByPortalID(int commandId, int userId, int portalId, bool Role_View, bool Role_Add, bool Role_Update, bool Role_Delete)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Users_UpdateCommandFromUserByPortalID", commandId, userId, portalId, Role_View, Role_Add, Role_Update, Role_Delete);
        }
        public override bool IsApproved(string strUsername)
        {
            UserInfo info = GetUser(strUsername);
            return info.IsApproved;
        }
        public override bool IsSuspended(string strUsername)
        {
            UserInfo info = GetUser(strUsername);
            if (DateTime.Compare(DateTime.Now, info.Suspended) < 0)
                return true;
            else
                return false;
        } 
        #endregion

        #region MainLanguage
        public override DataTable GetAll()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_MainLang_GetAll");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetAll_Without_Current_Language(string lang)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_MainLang_GetAll_Without_Current_Language", lang);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        #endregion       

        #region Command
        public override DataTable GetCommands()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Commands_GetAll");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override string GetCommandbyID(int commandid)
        {
            string retVal = string.Empty;
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Commands_By_ID", commandid);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dtSettings = ds.Tables[0];
                foreach (DataRow row in dtSettings.Rows)
                {
                    retVal = row["CommandKey"].ToString();
                }
            }
            return retVal;
        }        
        public override DataTable GetCommandsForUserNotGroup(int userId, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Commands_GetCommandsForUserNotGroupByPortalID", userId, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetCommandsForRoleByPortalID(int roleId, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Commands_GetCommandsForRoleByPortalID", roleId, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetCommandsForUserByPortalID(int userId, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Commands_GetCommandsForUserByPortalID", userId, portalId);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }      
        public override int AddCommand(CommandInfo command)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Commands_Insert", command.CommandName, command.CommandParentID,
                                        command.CommandKey, command.CommandParams, command.CommandUrl, command.CommandOrder,
                                                   command.CommandVisible, command.CommandEnable, command.IsSuperUser);
        }
        public override void UpdateCommand(CommandInfo command)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Commands_Update", command.CommandID, command.CommandName, command.CommandParentID,
                                      command.CommandKey, command.CommandParams, command.CommandUrl, command.CommandOrder,
                                      command.CommandVisible, command.CommandEnable, command.IsSuperUser);
        }
        public override void UpdateCommandbyControl(string old_cmd, string new_cmd)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Commands_UpdatebyControl", old_cmd, new_cmd);
        }
        public override void DeleteCommand(int commandId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Commands_Delete", commandId);
        }
        public override CommandInfo GetCommand(int commandId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Commands_GetInfo", commandId);
            CommandInfo command = (CommandInfo)ObjectHelper.CreateObject(typeof(CommandInfo), dr);
            return command;
        }       
        public override CommandInfo GetCommand(string commandKey)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Commands_GetInfoByCommandKey", commandKey);
            CommandInfo command = (CommandInfo)ObjectHelper.CreateObject(typeof(CommandInfo), dr);
            return command;
        }
        public override bool IsAvailableForUser(int commandId, int userId, int portalid)
        {
            DataTable dtCommandsForUser = GetCommandsForUserByPortalID(userId, portalid);
            foreach (DataRow row in dtCommandsForUser.Rows)
                if ((int)row["CommandID"] == commandId)
                    return true;
            return false;
        }
        public override bool IsSystemModule(int commandId)
        {
            CommandInfo info = GetCommand(commandId);
            return info.IsSuperUser;
        }        
        public override DataTable GetButtonRoleForUserByPortalID(int userId, int portalId)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Commands_GetButtonRoleForUserByPortalID", userId, portalId, AppEnv.CommandID());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        #endregion

        #region Tabs
        public override DataTable GetTabs(string lang)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Tabs_GetAll", lang);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetTabsByPortalID(int portalID, string lang)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Tabs_GetAllByPortalID", portalID, lang);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetTabs(int parent)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Tabs_GetByParent", parent);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetTabs(bool visible, bool enable, string lang)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, "Portal_Tabs_GetTabsVisibleAndEnable", visible, enable, lang);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override void AddControlToTab(int tabControlId, int tabId, int controlId, string layoutPosition, int order)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Tabs_AddControlToTab", tabControlId, tabId, controlId, layoutPosition, order);
        }
        public override void RemoveControlFromTab(int tabControlId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Tabs_RemoveControlFromTab", tabControlId);
        }
        public override int AddTab(TabInfo tab)
        {
            return (int)SqlHelper.ExecuteScalar(this.ConnectionString, "Portal_Tabs_Insert", tab.TabParentID, tab.TabName, tab.TabTitle,
                            tab.TabDescription, tab.TabUrl, tab.TabLayout, tab.TabVisible, tab.TabEnable, tab.TabOrder, tab.TabLang, tab.PortalID);
        }
        public override void UpdateTab(TabInfo tab)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Tabs_Update", tab.TabID, tab.TabParentID, tab.TabName, tab.TabTitle,
                                        tab.TabDescription, tab.TabUrl, tab.TabLayout, tab.TabVisible, tab.TabEnable, tab.TabOrder, tab.TabLang, tab.PortalID);
        }
        public override void UpdateTabControl(int tabControlId, int tabId, string layoutPosition, int order)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Tabs_UpdateTabControl", tabControlId, tabId, layoutPosition, order);
        }
        public override void DeleteTab(int tabId)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, "Portal_Tabs_Delete", tabId);
        }
        public override TabInfo GetTab(int TabId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Tabs_GetInfo", TabId);
            TabInfo tab = (TabInfo)ObjectHelper.CreateObject(typeof(TabInfo), dr);
            return tab;
        }
        public override TabControlInfo GetTabControl(int tabControlId)
        {
            IDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, "Portal_Tabs_GetTabControlInfo", tabControlId);
            TabControlInfo tab = (TabControlInfo)ObjectHelper.CreateObject(typeof(TabControlInfo), dr);
            return tab;
        }        
        #endregion        

        #region Infomation
        public override DataTable GetDatabaseInfo()
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "EXEC master.dbo.xp_msver");
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetDatabaseConfiguration()
        {
            StringBuilder strSQL = new StringBuilder();            
            strSQL.Append("SELECT   isnull(master.dbo.spt_values.name,master.dbo.sysconfigures.comment) AS Prameter_option, master.dbo.sysconfigures.[value], master.dbo.spt_values.low AS [Min], master.dbo.spt_values.high AS [Max], master.dbo.sysconfigures.status");
            strSQL.Append(" FROM         master.dbo.spt_values INNER JOIN master.dbo.sysconfigures ON master.dbo.spt_values.number = master.dbo.sysconfigures.config");
            strSQL.Append(" WHERE    master.dbo.spt_values.high is not null");
            strSQL.Append(" ORDER BY Prameter_option");

            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetOwners()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT name FROM sysusers WHERE uid!=0 AND isaliased=0 ORDER BY name");
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetFileGroups()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT groupname FROM sysfilegroups ORDER BY groupname");
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetDataTypes()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT USER_NAME(types1.uid), types1.name, types2.name, types1.length, types1.prec, types1.scale, ");
            strSQL.Append(" types1.allownulls, types1.usertype, OBJECT_NAME(types1.[domain]), OBJECT_NAME(types1.tdefault)");
            strSQL.Append(" FROM dbo.systypes types1 INNER JOIN");
            strSQL.Append(" dbo.systypes types2 ON types1.xtype = types2.xtype");
            strSQL.Append(" WHERE (types2.xusertype < 256) AND (types1.name NOT IN ('datetimn', 'decimaln', 'floatn', 'intn', 'moneyn', 'numericn'))");
            strSQL.Append(" ORDER BY types1.name");
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override DataTable GetDefaults()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT id, USER_NAME(uid)+'.'+name as name, USER_NAME(uid) as UserId, name as defaultName, crdate FROM sysobjects WHERE type='D' AND id NOT IN (SELECT constid FROM sysconstraints) ORDER BY 1");
                                  
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }         
        public override DataTable GetRules()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT id, USER_NAME(uid)+'.'+name as name, USER_NAME(uid) as owner,name as ruleName, crdate FROM sysobjects WHERE type='R' AND id NOT IN (SELECT constid FROM sysconstraints)  ORDER BY 1, 2");
                                  
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        } 
        #endregion

        #region Database
        public override DataTable GetTables()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT dbo.sysobjects.id as id, USER_NAME(dbo.sysobjects.uid) AS owner, dbo.sysobjects.name, USER_NAME(dbo.sysobjects.uid) + '.' + dbo.sysobjects.name as completename, dbo.sysindexes.[rows],");
            strSQL.Append(" FILEGROUP_NAME(dbo.sysindexes.groupid) AS filegroup, dbo.sysobjects.crdate");
            strSQL.Append(" FROM dbo.sysobjects INNER JOIN");
            strSQL.Append(" dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id");
            strSQL.Append(" WHERE (dbo.sysobjects.type IN ('U', 'TF')) AND (dbo.sysindexes.indid IN (0, 1)) AND (dbo.sysobjects.type <> 'S')  ORDER BY USER_NAME(dbo.sysobjects.uid), dbo.sysobjects.name");
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override void DropTable(string tblName)
        {
            string _sqlCommand = "DROP TABLE " + tblName;
            SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, _sqlCommand);
        }
        public override DataTable GetViews()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT sysobjects.id, USER_NAME(sysobjects.uid) as Owner,sysobjects.name, USER_NAME(sysobjects.uid) + '.' + sysobjects.name as completename,sysobjects.crdate,CASE WHEN sysindexes.id IS NULL THEN 'No' ELSE 'Yes' END AS Indexed ");
            strSQL.Append(" FROM sysobjects LEFT OUTER JOIN sysindexes ON sysobjects.id = sysindexes.id WHERE  (sysobjects.type = 'V')");
            strSQL.Append(" ORDER BY USER_NAME(sysobjects.uid),sysobjects.name");
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        } 
        public override DataTable GetStores()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT DISTINCT sysobjects.id, USER_NAME(uid) as owner, name, USER_NAME(uid) + '.' + name as completeName, sysobjects.crdate, IsEncrypted = CASE syscomments.encrypted WHEN 0 THEN 'No' ELSE 'Yes' END ");
            strSQL.Append(" FROM sysobjects, syscomments ");
            strSQL.Append(" WHERE (type='P' OR type='RF') AND sysobjects.id=syscomments.id ORDER BY owner,name");
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }     
        public override DataTable GetColums(string tblName)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("SELECT DISTINCT dbo.syscolumns.name, dbo.systypes.name AS datatype, dbo.systypes.type, dbo.syscolumns.length, dbo.syscolumns.prec,");
            strSQL.Append("				dbo.syscolumns.scale, dbo.syscolumns.colid, dbo.syscolumns.isnullable AS isnull, COLUMNPROPERTY(dbo.syscolumns.id, dbo.syscolumns.name, 'IsIdentity') AS [identity], isNull(OBJECT_NAME(syscolumns.cdefault),'') AS defConstraint, USER_NAME(O.uid) + '.' + O.name as ruleBind");
            strSQL.Append(" FROM        dbo.syscolumns LEFT OUTER JOIN");
            strSQL.Append("             dbo.systypes ON dbo.syscolumns.xusertype = dbo.systypes.xusertype LEFT OUTER JOIN");
            strSQL.Append("             dbo.sysindexkeys ON dbo.syscolumns.colid = dbo.sysindexkeys.colid AND dbo.syscolumns.id = dbo.sysindexkeys.id LEFT OUTER JOIN");
            strSQL.Append("             dbo.sysconstraints ON dbo.syscolumns.colid = dbo.sysconstraints.colid AND dbo.syscolumns.id = dbo.sysconstraints.id LEFT OUTER JOIN sysobjects O ON syscolumns.domain = O.id");
            strSQL.Append(" WHERE       (dbo.syscolumns.id = OBJECT_ID('" + tblName + "'))");
            strSQL.Append(" ORDER BY    dbo.syscolumns.colid");            
                        
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL.ToString());
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }         
        public override SqlDataReader LoadProperty(long _ID)
        {
            string _sqlCommand = "SELECT name, USER_NAME(uid) FROM sysobjects WHERE id = " + _ID.ToString();
            SqlDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, CommandType.Text, _sqlCommand);
            return dr;
        }
        public override SqlDataReader ReturnReader(string strSQL)
        {
            SqlDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, CommandType.Text, strSQL);
            return dr;
        }        
        public override void ExecuteCommand(string strSQL)
        {
            SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, strSQL);            
        }
        public override DataTable ReturnDataSet(string strSQL)
        {
            DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, strSQL);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public override SqlCommand GetProcedureCommand(string procedureName)
        {
            SqlCommand _OledbCommand = new SqlCommand();
            SqlConnection con = new SqlConnection(this.ConnectionString);
            _OledbCommand.Connection = con;
            _OledbCommand.CommandText = procedureName;
            _OledbCommand.CommandTimeout = 300;
            _OledbCommand.CommandType = CommandType.StoredProcedure;
            return _OledbCommand;
        }
        
        #endregion

    }
}

